﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Project_ConnectSql.DA
{
    class DataAccess
    {

        public string ConnectString = "Data Source=TRAVUONG;Initial Catalog=SPSHOP;Integrated Security=True";
        private SqlCommand cmd;
        private SqlConnection conn;
        private SqlDataAdapter da;
        /// <summary>
        /// Mở kết nối 
        /// </summary>
        private void OpenConnect()
        {
            if (conn ==null)
            //khởi tạo kết nói csdl
            {  conn = new SqlConnection(ConnectString); }
            // kiểm tra trạng thái kết nối
            if(conn != null && conn.State == ConnectionState.Closed )
            {  conn.Open();       }
          }
        
        private void CloseConnect()
        {  if (conn != null && conn.State == ConnectionState.Open)
            
        // nếu đóng thì mở kết nối
        {   conn.Close();  }  }


        /// <summary>
        /// Lấy dữ liệu từ CSDL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetData(string sql)
        {
            OpenConnect();
            DataTable dt = new DataTable();
            cmd = new SqlCommand(sql, conn);
            da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            CloseConnect();
            return dt ;
         }

        /// <summary>
        /// Lấy dữ liệu chỉ 1 là số
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>chỉ 1 giá trị là số </returns>
        public int GetNumberData(string sql)
        {
            OpenConnect();
            SqlCommand cmd = new SqlCommand(sql, conn);
             int count  = int.Parse(cmd.ExecuteScalar().ToString());
            CloseConnect();
            return count;
        }

        /// <summary>
        /// Lấy dữ liệu chỉ 1 là số
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>chỉ 1 giá trị là ký tự</returns>
        public string GetStringData(string sql)
        {
            OpenConnect();
            SqlCommand cmd = new SqlCommand(sql, conn);
            string str = cmd.ExecuteScalar().ToString();
            CloseConnect();
            return str;
        }

        /// <summary>
        /// Insert, update , delete
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        /// 
        public bool Excute(string sql)
        {
             OpenConnect();
            SqlCommand cmd = new SqlCommand(sql, conn);
             cmd.ExecuteNonQuery();
            CloseConnect();
            return true;
        }

        public DataTable GetDataLoaihang(string teLoaiHang)
        {
            //SqlConnection conn = new SqlConnection(ConnectString);
            //conn.Open();
            string sql = "select*from tbl_LoaiHang where TenLoaiHang = N'"+teLoaiHang +"'";
            //SqlCommand cmd = new SqlCommand(sql, conn);
            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
           // da.Fill(dt);
            dt = GetData(sql);
           // conn.Close();
            return dt;
        }
            public DataTable GetDataLoaihang()
        {
            //SqlConnection conn = new SqlConnection(ConnectString);
            //conn.Open();
            string sql = "select*from tbl_LoaiHang";
            //SqlCommand cmd = new SqlCommand(sql, conn);
            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            // da.Fill(dt);
            dt = GetData(sql);
            // conn.Close();
            return dt;
        }

        public int GetCountLoaihang()
        {
            //string ConnectString = "Data Source=TRAVUONG;Initial Catalog=SPSHOP;Integrated Security=True";
           // SqlConnection conn = new SqlConnection(ConnectString);
           // conn.Open();
            string sql = "select count(*) from tbl_LoaiHang";
          //  SqlCommand cmd = new SqlCommand(sql, conn);
           // int count = int.Parse(cmd.ExecuteScalar().ToString());
            int count = GetNumberData(sql);
           // conn.Close();
            return count;
        }

        public bool InsertLoaihang(string tenLoaiHang)
        {
            //string ConnectString = "Data Source=TRAVUONG;Initial Catalog=SPSHOP;Integrated Security=True";
           // SqlConnection conn = new SqlConnection(ConnectString);
            //conn.Open();
            string sql = "insert into tbl_LoaiHang (TenLoaiHang) Values (N'"+ tenLoaiHang +"')";
            // SqlCommand cmd = new SqlCommand(sql, conn);
            //cmd.ExecuteNonQuery(); 
            Excute(sql);
           // conn.Close();
            return true;
        }
    }
}
